package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Trained;
import ModelLayer.ResponseList;
//
public class DBResponseList implements DBResponseListIF
{
	private Connection con;
    
    public DBResponseList()
    {
    	con = DBConnection.getInstance().getDBcon();
    }
	
    public ArrayList<ResponseList> getAllResponseList(boolean retriveAssociation)
    {
    	return miscWhere("", retriveAssociation);
    }

    public ResponseList searchResponseListID(int responseListID, boolean retriveAssociation)
    {   
    	String wClause = "rl.ResponseListID = " + responseListID + "";
        return singleWhere(wClause, retriveAssociation);
    }
    
    public ResponseList searchResponseListByTrainedName(String trainedName, boolean retriveAssociation)
    {   
    	String wClause = "t.Name = '" + trainedName + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    
    private ArrayList<ResponseList> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<ResponseList> responseListList = new ArrayList<ResponseList>();	
		
		String query = buildQuery(wClause);
  
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
			while( results.next() )
			{
				ResponseList rlObj = new ResponseList();
				rlObj = buildResponseList(results);	
				responseListList.add(rlObj);	
			}
			stmt.close();
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return responseListList;
	}
	
	private ResponseList singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ResponseList rlObj = new ResponseList();
	    String query = buildQuery(wClause);
		try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			rlObj = buildResponseList(results);
	 			stmt.close();
			}
		}	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return rlObj;
	}

	private String buildQuery(String wClause)
	{
	    String query="SELECT rl.ResponseListID, rl.Response, rl.MasterCriterias, rl.Number, rl.Assignment, rl.Introduced, rl.Mastered, rl.IntroSignature, rl.MastSignature, t.Name FROM Trained t, ResponseList rl";
	    query=query+" WHERE t.TrainedID = rl.TrainedID AND rl.ResponseListID = rl.ResponseListID";
		if (wClause.length()>0)
			query=query+" AND "+ wClause;
		return query;
	}

	private ResponseList buildResponseList(ResultSet results)
    {   
		ResponseList rlObj = new ResponseList();
		Trained trained = new Trained();
        try
        {
        	rlObj.setResponseListID(results.getInt("ResponseListID"));
        	rlObj.setResponse(results.getString("Response"));
        	rlObj.setMasterCriterias(results.getString("MasterCriterias"));
        	rlObj.setNumber(results.getInt("Number"));
        	rlObj.setAssignment(results.getString("Assignment"));
        	rlObj.setIntroduced(results.getDate("Introduced"));
        	rlObj.setMastered(results.getDate("Mastered"));
        	rlObj.setIntroSignature(results.getString("IntroSignature"));
        	rlObj.setMastSignature(results.getString("MastSignature"));
        	trained.setName(results.getString("Name"));
        	rlObj.setTrained(trained);
         }
         catch(Exception e)
         {
             System.out.println("error in building the ResponseList object");
         }
         return rlObj;
     }
}
